package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import str.Language;
import util.Constants;

import org.apache.log4j.Logger;

public class DbLanguages {
    public static Logger LOG = Logger.getLogger("db.DbLanguages");

    // add new language
    public static boolean addLanguage(String name) {
        name = name.trim();
        if (name == null) {
            return false;
        }
        if (name == "") {
            return false;
        }
        if (name.length() == 0) {
            return false;
        }
        boolean bOK = false;
        Connection cnn = null;
        PreparedStatement pstatCheck = null;
        CallableStatement cstatInsert = null;
        ResultSet rs = null;
        try {
            cnn = DbController.getConnection();
            pstatCheck = cnn.prepareStatement("select*from m_languages where m_name='" + name + "'");
            rs = pstatCheck.executeQuery();
            if (rs.next() == false) {
                pstatCheck.close();
                cstatInsert = cnn.prepareCall("{call m_languages_insert(?)}");
                cstatInsert.setString(1, name);                        
                bOK = (cstatInsert.executeUpdate() == 1);
            }
        } catch (Exception ex) {
            LOG.error("addLanguage", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstatCheck != null) {
                    pstatCheck.close();
                }
                if (cstatInsert != null) {
                    cstatInsert.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception exx) {
            }
        }
        return bOK;
    }

    // remove language by its name
    public static boolean removeLanguage(String name) {
        Connection cnn = null;
        PreparedStatement pstatsel = null;
        ResultSet rs = null;
        int langID = -1;
        try {
            cnn = DbController.getConnection();
            pstatsel = cnn.prepareStatement("select m_id from m_languages where m_name=N'" + name + "'");
            rs = pstatsel.executeQuery();
            if (rs.next()) {
                langID = rs.getInt("m_id");
            }
            rs.close();
            pstatsel.close();
        } catch (Exception ex) {
            LOG.error("removeLanguage(String)", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstatsel != null) {
                    pstatsel.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception exx) {
            }
        }
        if (langID != -1) {
            return DbLanguages.removeLanguage(langID);
        } else {
            return false;
        }
    }

    // remove language by its ID
    public static boolean removeLanguage(int langID) {
        boolean bOK = false;
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        DbDictionaries dictList = new DbDictionaries();
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("select m_id from m_dictionaries where m_fromID=" + langID + " or m_toID="
                    + langID + " group by m_id");
            rs = pstat.executeQuery();
            
            while (rs.next()) {
                dictList.deleteDict(rs.getInt("m_id"));
            }
            rs.close();
            pstat.close();
            pstat = cnn.prepareStatement("delete from m_languages where m_id=" + langID);
            bOK = (pstat.executeUpdate() == 1);
        } catch (Exception ex) {
            LOG.error("removeLanguage(int)", ex);
        } finally {
            try {
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception exx) {
            }
        }
        return bOK;
    }

    public static Language[] getLangList() {
        Connection cnn = null;
        PreparedStatement pstat = null;
        Language[] list = null;
        ResultSet rs = null;
        int length = 0;
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("select count(*) as LangNum from m_languages");
            rs = pstat.executeQuery();
            if (rs.next()) {
                length = rs.getInt("LangNum");
            }
            if (length > 0) {
                list = new Language[length];
                rs = pstat.executeQuery("select m_id,m_name from m_languages");
            }
            for (int i = 0; i < length; i++) {
                list[i] = new Language();
                if (rs.next()) {
                    list[i].id = rs.getInt("m_id");
                    list[i].name = rs.getString("m_name");
                } else {
                    list[i].id = list[0].id;
                    list[i].name = list[0].name;
                }
            }
        } catch (Exception ex) {
            LOG.error("getLangList", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return list;
    }

    public static String getLangListHTML() {
        String list = Constants.BLANK_STRING;
        Connection cnn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            cnn = DbController.getConnection();
            pstat = cnn.prepareStatement("select m_id,m_name from m_languages");
            rs = pstat.executeQuery();
            while (rs.next()) {
                list += "<option value=" + rs.getInt("m_id") + ">" + rs.getString("m_name") + "</option>";
            }
        } catch (Exception ex) {
            LOG.error("getLangListHTML", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstat != null) {
                    pstat.close();
                }
                if (cnn != null) {
                    cnn.close();
                }
            } catch (Exception ex) {
            }
        }
        return list;
    }
    
    public static void main(String args[]){
    	System.out.println(getLangListHTML());
    	
    }
    
}
